﻿
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.TMS.VehicleTransportFaq;

namespace BCData.TMS.VehicleTransportFaq
{
    public class VehicleTransportFaqData : IVehicleTransportFaqData
    {
        private readonly IDatabaseContext database;
        private readonly string QUERY = @"SELECT
		    VehicleTransportFaqCategoryId,
		    VehicleTransportFaqId,
		    Title,
		    Question,
		    Answer,
		    KeyWord,
		    IsShow,
		    IsDelete,
		    IsReply,
		    IsSolve,
		    Enabled,
		    CreateTime,
		    ReplyTime,
		    ReplyEmail,
		    AdminId,
		    AdminFullName
        FROM
            vehicletransportfaq";



        #region 构造函数
        public VehicleTransportFaqData(IDatabaseContext databaseContext)
        {
            this.database = databaseContext;
        }
        #endregion

        #region InsertAsync
        /// <summary>
        /// 添加新车辆运输常见问题纪录
        /// </summary>
        /// <param name="vehicleTransportFaqEntity"></param>
        /// <returns></returns>
        public async Task<VehicleTransportFaqEntity> InsertAsync(VehicleTransportFaqEntity entity)
        {
            return await this.database.Master.InsertAsync(entity,database.Transaction);
        }
        #endregion

        #region updateAsync
        public async Task<VehicleTransportFaqEntity> UpdateAsync(VehicleTransportFaqEntity entity)
        {
            return await this.database.Master.UpdateAsync(entity, database.Transaction);
        }
        #endregion 

        #region Delete
        public async Task<bool> DeleteAsync(long vehicleTransportFaqId)
        {
            string sql = "DELETE FROM vehicletransportfaq WHERE VehicleTransportFaqId = ?vehicleTransportFaqId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTransportFaqId", vehicleTransportFaqId, DbType.Int64);
            return await this.database.Master.ExecuteAsync(sql, parameters, database.Transaction) > 0;
        }
        #endregion

        #region GetById
        public async Task<VehicleTransportFaqEntity> GetEntityAsync(long vehicleTransportFaqId)
        {
            string sql = QUERY + " WHERE  VehicleTransportFaqId = ?vehicleTransportFaqId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?VehicleTransportFaqId", vehicleTransportFaqId, DbType.Int64);
            return await this.database.Slave.QueryFirstOrDefaultAsync<VehicleTransportFaqEntity>(sql, parameters);
        }
        #endregion

        #region GetListAsync 查询车辆运输常见问题记录
        public async Task<(IEnumerable<VehicleTransportFaqEntity>, int)> GetListAsync(int? faqCategoryId, string title, string question, string answer, string keyWord, bool? isShow, bool? isDelete, bool? isReply, bool? isSolve, bool? enable, DateTime? start, DateTime? end, string replyEmail, string sortName, bool? ascending, int pageIndex, int pageSize)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            if(faqCategoryId.HasValue)
            {
                wheres.Add("VehicleTransportFaqCategoryId=?VehicleTransportFaqCategoryId");
                parameters.Add("?VehicleTransportFaqCategoryId", faqCategoryId.Value, DbType.Int32);
            }
            if(!string.IsNullOrWhiteSpace(title))
            {
                wheres.Add("Title LIKE ?Title");
                parameters.Add("?Title", $"%{title}%",DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(question))
            {
                wheres.Add("Question LIKE ?Question");
                parameters.Add("?Question", $"%{question}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(answer))
            {
                wheres.Add("Answer LIKE ?Answer");
                parameters.Add("?Answer", $"%{answer}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                wheres.Add("KeyWord LIKE ?KeyWord");
                parameters.Add("?KeyWord", $"%{keyWord}%", DbType.AnsiString);
            }
            if(isShow.HasValue)
            {
                wheres.Add("IsShow=?IsShow");
                parameters.Add("?IsShow", isShow.Value, DbType.Boolean);
            }
            if (isDelete.HasValue)
            {
                wheres.Add("IsDelete=?IsDelete");
                parameters.Add("?IsDelete", isDelete.Value, DbType.Boolean);
            }
            if (isReply.HasValue)
            {
                wheres.Add("IsReply=?IsReply");
                parameters.Add("?IsReply", isReply.Value, DbType.Boolean);
            }
            if (isSolve.HasValue)
            {
                wheres.Add("IsSolve=?IsSolve");
                parameters.Add("?IsSolve", isSolve.Value, DbType.Boolean);
            }
            if (enable.HasValue)
            {
                wheres.Add("Enabled=?Enabled");
                parameters.Add("?Enabled", enable.Value, DbType.Boolean);
            }

            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }

            if(!string.IsNullOrWhiteSpace(replyEmail))
            {
                wheres.Add("ReplyEmail LIKE ?ReplyEmail");
                parameters.Add("?ReplyEmail", $"%{keyWord}%", DbType.AnsiString);
            }


            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3} ; SELECT COUNT(1) FROM  vehicletransportfaq  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await this.database.Slave.QueryPageableListAsync<VehicleTransportFaqEntity>(sql, parameters);
        }
        #endregion



        #region GetListAsync 查询车辆运输常见问题记录
        public async Task<IEnumerable<VehicleTransportFaqEntity>> GetListAsync(int? faqCategoryId, string title, string question, string answer, string keyWord, bool? isShow, bool? isDelete, bool? isReply, bool? isSolve, bool? enable, DateTime? start, DateTime? end, string replyEmail, string sortName, bool? ascending)
        {

            DynamicParameters parameters = new DynamicParameters();
            //查询条件
            List<string> wheres = new List<string>();
            if (faqCategoryId.HasValue)
            {
                wheres.Add("VehicleTransportFaqCategoryId=?VehicleTransportFaqCategoryId");
                parameters.Add("?VehicleTransportFaqCategoryId", faqCategoryId.Value, DbType.Int32);
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                wheres.Add("Title LIKE ?Title");
                parameters.Add("?Title", $"%{title}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(question))
            {
                wheres.Add("Question LIKE ?Question");
                parameters.Add("?Question", $"%{question}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(answer))
            {
                wheres.Add("Answer LIKE ?Answer");
                parameters.Add("?Answer", $"%{answer}%", DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(keyWord))
            {
                wheres.Add("KeyWord LIKE ?KeyWord");
                parameters.Add("?KeyWord", $"%{keyWord}%", DbType.AnsiString);
            }
            if (isShow.HasValue)
            {
                wheres.Add("IsShow=?IsShow");
                parameters.Add("?IsShow", isShow.Value, DbType.Boolean);
            }
            if (isDelete.HasValue)
            {
                wheres.Add("IsDelete=?IsDelete");
                parameters.Add("?IsDelete", isDelete.Value, DbType.Boolean);
            }
            if (isReply.HasValue)
            {
                wheres.Add("IsReply=?IsReply");
                parameters.Add("?IsReply", isReply.Value, DbType.Boolean);
            }
            if (isSolve.HasValue)
            {
                wheres.Add("IsSolve=?IsSolve");
                parameters.Add("?IsSolve", isSolve.Value, DbType.Boolean);
            }
            if (enable.HasValue)
            {
                wheres.Add("Enabled=?Enabled");
                parameters.Add("?Enabled", enable.Value, DbType.Boolean);
            }

            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }

            if (!string.IsNullOrWhiteSpace(replyEmail))
            {
                wheres.Add("ReplyEmail LIKE ?ReplyEmail");
                parameters.Add("?ReplyEmail", $"%{keyWord}%", DbType.AnsiString);
            }


            string sql = string.Format(@"{0} {1} ORDER BY {2} {3} ; SELECT COUNT(1) FROM  vehicletransportfaq  {1}",
                   QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "", 
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            return await this.database.Slave.QueryAsync<VehicleTransportFaqEntity>(sql, parameters);
        }
        #endregion
    }
}
//接口



